package ibms_rcp.dao;

import ibms_rcp.constants.Constants;
import ibms_rcp.db.DataBaseManager;
import ibms_rcp.model.BillLog;
import ibms_rcp.util.SqlUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

/*
 * 账单管理，主要记录所有业主用户的账单，不论业主是否开通了预收费
 * 所有业主应该付的都会记录在账单记录表中
 * 只有业主公司开通了预收费业主，才会自动收费，并且记录收费记录表。
 */
public class BillManager {
	/*
	 * 获取登记的所有公司的moi_id后，保存所有公司的账单（目前按天计算，模拟过程中可以每点击结算按钮就计算一次）
	 */
	public static void saveAllBillLogs() {

		ArrayList<Long> allCompanyMois = SqlUtil.getAllCompanyMoi_id();
		for (Long companymoi : allCompanyMois) {
			saveBillLogsbyCompany_moi_id(companymoi);
		}
	}

	/*
	 * 根据一个公司的moi_id，保存该一个公司的账单（目前按天计算，模拟过程中可以每点击结算按钮就计算一次）
	 */
	public static void saveBillLogsbyCompany_moi_id(Long companymoi) {
		String name = SqlUtil.getCompanyNamebyCompanyMoi_id(companymoi);
		if (name != null) {
			ArrayList<Long> allroomMois = SqlUtil.getAllRoomMoi_id(companymoi);
			for (Long roommoi : allroomMois) {
				String bu = SqlUtil.getBuFlRoInfo(roommoi)[0];
				String fl = SqlUtil.getBuFlRoInfo(roommoi)[1];
				String ro = SqlUtil.getBuFlRoInfo(roommoi)[2];
				ArrayList<Long> allAmmeterMois = SqlUtil
						.getAllAmmeterMoi_id(roommoi);
				for (Long ammetermoi : allAmmeterMois) {
					saveOneBillLog(companymoi, name, roommoi, bu, fl, ro,
							ammetermoi);
				}
			}
		}
	}

	// 保存一条账单记录
	public static void saveOneBillLog(Long companymoi, String name,
			Long roommoi, String bu, String fl, String ro, Long ammetermoi) {
		BillLog bLog = new BillLog();
		bLog.setCompany_moi_id(companymoi);
		bLog.setCompany_name(name);
		bLog.setRoom_moi_id(roommoi);
		bLog.setBu(bu);
		bLog.setFl(fl);
		bLog.setRo(ro);
		bLog.setBilltime(SqlUtil.getTimeStamp());
		bLog.setDealflag(false);
		bLog = setAmmeterInfo(bLog, ammetermoi);

		if(! bLog.getOldvalue().endsWith(bLog.getNowvalue())){
			saveOneBillLoginDB(bLog, ammetermoi);
		}

	}

	// 设置一条账单的详情，主要是通过电表查询读数，计算金额
	public static BillLog setAmmeterInfo(BillLog bl, Long am) {
		BillLog bLog = bl;
		double price = 0;
		String olddegree = null;
		String nowdegree = null;
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select new_value from m2mnap_moi_attribute "
					+ " where moi_id = ? and moc_attribute_code = ?";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, am);
			pstmt.setString(2, "type");
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				String ty = rs.getString(1);
				if (!ty.endsWith("电费")) {
					ty += "电费";
				}
				bLog.setBill_type(ty);
			}

			pstmt.setString(2, "price");
			rs = pstmt.executeQuery();
			if (rs.next()) {
				price = Double.valueOf(rs.getString(1));
			}

			pstmt.setString(2, "degree");
			rs = pstmt.executeQuery();
			if (rs.next()) {
				olddegree = rs.getString(1);
			}
			// 此处使用申哥提供的借口，获取最新的电表读数，测试时，先定死为255；
			nowdegree = "255";
			bLog.setOldvalue(olddegree);
			bLog.setNowvalue(nowdegree);
			String cost = Double.toString(Double.valueOf(bLog.getNowvalue()) - Double.valueOf(bLog.getOldvalue()));
			bLog.setCostvalue(cost);
			bLog.setBill_point(price * Double.valueOf(bLog.getCostvalue()));

			rs.close();
			pstmt.close();

		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return bLog;
	}

	// 将准备好的一条账单保存进数据库中，更新电表的上次记录读数
	public static void saveOneBillLoginDB(BillLog bl, Long am) {
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "insert into m2mnap_estate_bill_log"
					+ "(id,company_moi_id,company_name,room_moi_id,buildingid,floorid,"
					+ "roomid,bill_time,bill_type,oldvalue,newvalue,costvalue,bill_point,dealflag) values "
					+ "( nextval('m2mnap_estate_bill_log_seq'),?,?,?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, bl.getCompany_moi_id());
			pstmt.setString(2, bl.getCompany_name());
			pstmt.setLong(3, bl.getRoom_moi_id());
			pstmt.setString(4, bl.getBu());
			pstmt.setString(5, bl.getFl());
			pstmt.setString(6, bl.getRo());
			pstmt.setTimestamp(7, bl.getBilltime());
			pstmt.setString(8, bl.getBill_type());
			pstmt.setString(9, bl.getOldvalue());
			pstmt.setString(10, bl.getNowvalue());
			pstmt.setString(11, bl.getCostvalue());
			pstmt.setDouble(12, bl.getBill_point());
			pstmt.setBoolean(13, bl.isDealflag());

			pstmt.executeUpdate();

			sqlString = "update m2mnap_moi_attribute set old_value = ?,new_value =? where "
					+ "moi_id = ? and moc_attribute_code = ?";
			pstmt = conn.prepareStatement(sqlString);
			pstmt.setString(1, bl.getOldvalue());
			pstmt.setString(2, bl.getNowvalue());
			pstmt.setLong(3, am);
			pstmt.setString(4, "degree");

			pstmt.executeUpdate();

			pstmt.close();
			conn.commit();

		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}

	// 获得所有公司的全部账单（包含已付和未付）
	public static ArrayList<BillLog> getAllBillLogs() {
		ArrayList<BillLog> bLogs = new ArrayList<BillLog>();

		ArrayList<Long> allCompanyMois = SqlUtil.getAllCompanyMoi_id();
		for (Long companymoi : allCompanyMois) {
			bLogs.addAll(getAllBillLogsbyCompany_moi_id(companymoi));
		}
		return bLogs;
	}

	// 依据公司moi_id,获得一个公司的全部账单（包含已付和未付）
	public static ArrayList<BillLog> getAllBillLogsbyCompany_moi_id(
			Long company_moi_id) {
		ArrayList<BillLog> bLogs = new ArrayList<BillLog>();
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select * from m2mnap_estate_bill_log where bill_type like ? and company_moi_id = ? order by id";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setString(1, "?电费");
			pstmt.setLong(2, company_moi_id);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				BillLog bLog = setBillLog(rs, conn);
				bLogs.add(bLog);
				//System.out.println(bLog.getId());
			}
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

		return bLogs;
	}

	// 依据公司moi_id,获得一个公司的全部已付账单
	public static ArrayList<BillLog> getTrueBillLogsbyCompany_moi_id(
			Long company_moi_id) {
		ArrayList<BillLog> bLogs = new ArrayList<BillLog>();
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select * from m2mnap_estate_bill_log where bill_type like ? and company_moi_id = ? and dealflag=? order by id";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setString(1, "?电费");
			pstmt.setLong(2, company_moi_id);
			pstmt.setString(3, "TRUE");
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				BillLog bLog = setBillLog(rs, conn);
				bLogs.add(bLog);
			}
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

		return bLogs;
	}

	// 依据公司moi_id,获得一个公司的全部未付账单
	public static ArrayList<BillLog> getFalseBillLogsbyCompany_moi_id(
			Long company_moi_id) {
		ArrayList<BillLog> bLogs = new ArrayList<BillLog>();
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select * from m2mnap_estate_bill_log where bill_type like ? and company_moi_id = ? and dealflag=? order by id";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setString(1, "?电费");
			pstmt.setLong(2, company_moi_id);
			pstmt.setBoolean(3, false);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				BillLog bLog = setBillLog(rs, conn);
				bLogs.add(bLog);
			}
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

		return bLogs;
	}

	// 依据账单id号,获取账单信息
	public static BillLog getOneBillLogsbybill_id(Long bill_id) {
		BillLog bLog = new BillLog();
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select * from m2mnap_estate_bill_log where id = ?";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, bill_id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				bLog = setBillLog(rs, conn);
			}
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

		return bLog;
	}
	
	public static BillLog setBillLog(ResultSet rs,Connection conn){
		BillLog bLog = new BillLog();
		try{
			bLog.setId(rs.getLong("id"));
			bLog.setCompany_moi_id(rs.getLong("company_moi_id"));
			bLog.setCompany_name(rs.getString("company_name"));
			bLog.setRoom_moi_id(rs.getLong("room_moi_id"));
			bLog.setBu(rs.getString("buildingid"));
			bLog.setFl(rs.getString("floorid"));
			bLog.setRo(rs.getString("roomid"));
			bLog.setBilltime(rs.getTimestamp("bill_time"));
			bLog.setBill_type(rs.getString("bill_type"));
			bLog.setOldvalue(rs.getString("oldvalue"));
			bLog.setNowvalue(rs.getString("newvalue"));
			
			bLog.setCostvalue(rs.getString("costvalue"));
			bLog.setBill_point(rs.getDouble("bill_point"));
			bLog.setDealflag(rs.getBoolean("dealflag"));
			//System.out.println(bLog.getId());
		}catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return bLog;
	}

	public static void main(String args[]) {
		BillManager.saveAllBillLogs();
		//BillManager.getAllBillLogs();
	}
}
